<?php
/*
 * Created on Apr 1, 2006
 *
 */
 
require_once('sql_connection.php');
require_once('sql_offender.php');

define('OPT_NONE', 0);
define('OPT_GET_COUNT', 1);

define('AREA_FILTER_UNSPECIFIED', 'unspecified');

 
class Sql_incident extends Sql_connection {

 	function __construct() {
		parent :: __construct("Incident");		
 	}
 	
 	/**
 	 * Function to insert a new incident into the database.
 	 */
 	function insert_incident($area, $time, $offense, $offender, $lon, $lat, $comment) {
 		
 		$sql_area = new Sql_area();
 		$sql_offense = new Sql_offense();
                $sql_offender = new Sql_offender();
 		$user = get_user();

		if ($area and !$sql_area->exists($area))
			die("insert_incident - Area does not exist.");
		
		if (!$sql_offense->exists($offense))
			die("insert_incident - Offense does not exist.");

                // lon and lat need to be NULL if they don't have a value,
                // or mysql will default them to 0.0.
                $lon = is_numeric($lon) ? "'$lon'" : "NULL";
                $lat = is_numeric($lat) ? "'$lat'" : "NULL";

                $offender = is_numeric($offender) ? "'$offender'" : "NULL";
                $time     = is_numeric($time) ? "FROM_UNIXTIME($time)" : "NULL";
                $area = mysql_input($area);
                $area = ($area) ? "'$area'" : "NULL";

		$query = sprintf("INSERT INTO Incident
 				(user, area, incident_time, 
                                    offense, offender, lon, lat, comment, submitted_time)
 				VALUES ('%s',%s,%s,'%s',%s,%s,%s,'%s',NOW())",
 					mysql_input($user),
 					$area,
 					$time,
 					mysql_input($offense),
 					$offender,
 					$lon,
 					$lat,
 					mysql_input($comment));


 		$result = mysql_query($query);
 		if (!$result)
 			die ("Could not send query to DB:" . mysql_error());
 		
 		return mysql_insert_id();
 	}
 	
 	function get_incident($id) {
 		if (!is_numeric($id))
 			die ("get_incident() - got invalid ID type.");
 		
 		$query = "SELECT id, user, area, incident_time,
 				offense, offender, lat, lon, comment
 				FROM Incident WHERE id = '$id'";
 				
 		$result = mysql_query($query);
 		if (!$result)
 			die ("Could not send query to DB:" . mysql_error());
 			
 		$row = mysql_fetch_assoc($result);
 			
 		return $row;
 	}
 	
 	function get_all() {
 		
 		$query = "SELECT id, user, area, incident_Time,
 				offense, offender, lat, lon, comment
 				FROM Incident ORDER BY date DESC, time DESC";
 				
 		$result = mysql_query($query);
 		if (!$result)
 			die ("Could not send query to DB:" . mysql_error());
 			
 		$incidents = Array();
 		while ($row = mysql_fetch_assoc($result))
 			$incidents[] = $row;
 			
 		return $incidents;
 		
 		
 	}
 	
 	/**
 	 * Function to get the last x incidents.
 	 */
 	public function get_last($offset, $count, $opt = OPT_NONE) {
            $order = "id DESC";
            return $this->get_incidents($offset, $count, $opt, null, $order);
	}

        
 	public function get_users() {
 		$query = "SELECT DISTINCT user FROM Incident ORDER BY user DESC";
 		return column_as_array(mysql_query($query));
 	}
        
        public function get_by_area($offset, $count, $area, $opt = OPT_NONE) {
            $clause;
            if ($area == AREA_FILTER_UNSPECIFIED)
                $clause = "area IS NULL";
            else 
                $clause = "area = '" . mysql_input($area) . "'";

            $order = "area DESC";

            return $this->get_incidents($offset, $count, $opt, $clause, $order);
        }

 	public function get_by_offense($offset, $count, $offense, $opt) {
            $offense = mysql_input($offense);
            $clause = "offense = '$offense'";
            $order = "offense DESC";
            return $this->get_incidents($offset, $count, $opt, $clause, $order);
        }
 	
        public function get_by_user($offset, $count, $user, $opt) {
            $user = mysql_input($user);
            $clause = "user = '$user'";
            $order = "user DESC";
            return $this->get_incidents($offset, $count, $opt, $clause, $order);
        }

        public function get_by_offender($offender_id, $opt) {
            assert(is_numeric($offender_id));
            return $this->get_incidents(null, null, $opt, "offender = $offender_id");
        }

        private function get_incidents($offset, $count, $opt, $clause, $order = null) {
            assert(is_null($offset) || is_numeric($offset));
            assert(is_null($count) || is_numeric($count));

            $query = "SELECT * FROM Incident";
            if (!is_null($clause))
                $query .= " WHERE $clause";
            if (!is_null($order))
                $query .= " ORDER BY $order";
            if (!is_null($offset) and !is_null($count)) 
                $query .= " LIMIT $offset, $count";


            $incidents = get_results($query);

            switch ($opt) {
                case OPT_GET_COUNT:
                    return array($incidents, $this->get_count($clause));
                case OPT_NONE:
                    return $incidents;
                default:
                    print "Unknown option $opt";
                    assert(false);
            }
        }

        private function get_count($clause = null) {
            $query = "SELECT count(id) FROM Incident";
            if ($clause)
                $query .= " WHERE $clause";

            $res = get_single_result($query);
            return $res[0];
        }

}
?>
